SET QUOTED_IDENTIFIER OFF
GO


-- Given the HierarchyKey of the parent folder, gets a sort order that will put the new folder in alphabetical order.
-- if the parent folder does not exist in the db (i.e., it is only in memory at this point) rootHierarchyKey and
-- parentFolderSortOrder will be used to determine the new sort order.
CREATE PROCEDURE [dbo].[asi_HierarchyGetFolderSortOut]
   @parentFolderHierarchyKey uniqueidentifier,
   @rootHierarchyKey uniqueidentifier,
   @parentFolderSortOrder int,
   @parentFolderDepth int,
   @newFolderName nvarchar(100),
   @newSort int OUTPUT
AS
BEGIN
    DECLARE @lowSort int
    DECLARE @highSort int
    DECLARE @lowDocumentName nvarchar(100)
    DECLARE @Run int

    SET @Run = 0
    WHILE @Run < 2
    BEGIN
        -- find the top end of the sort range.  Which is from the next folder up in the tree from where we want to be
        SELECT @highSort = Min(SortOrder)
          FROM Hierarchy a INNER JOIN DocumentMain b
            ON a.UniformKey = b.DocumentVersionKey AND b.DocumentStatusCode = 40
         WHERE (a.ParentHierarchyKey = @parentFolderHierarchyKey
           AND b.DocumentName > @newFolderName
           AND b.DocumentTypeCode = 'FOL')
            OR (a.ParentHierarchyKey = @parentFolderHierarchyKey
           AND a.SortOrder > @parentFolderSortOrder
           AND a.Depth <= @parentFolderDepth)

        -- if there is nothing higher (i.e., this is at the very end of the tree), just get the tree's max + 2048
        IF @highSort IS NULL
            SELECT @highSort = IsNull(Max(SortOrder),0) + 2048
              FROM Hierarchy
             WHERE RootHierarchyKey = @rootHierarchyKey

        -- find the highest sort order below our high.  That will be the low
        SELECT @lowSort = IsNull(Max(SortOrder),0)
          FROM Hierarchy
         WHERE RootHierarchyKey = @rootHierarchyKey
           AND SortOrder < @highSort

        IF @lowSort < @parentFolderSortOrder
        BEGIN
            IF @Run = 0
                SET @lowSort = @parentFolderSortOrder
            ELSE
            BEGIN
                SET @lowSort = @parentFolderSortOrder
                IF @highSort - @lowSort < 2
                    SET @highSort = @lowSort + 2
            END
        END

        -- if there is no room between the low and the high, need to redo the sort order for this root
        IF @highSort - @lowSort < 2
        BEGIN
            EXEC asi_HierarchyResort2 @rootHierarchyKey

            -- get the sort again in case it has changed
            SELECT @parentFolderSortOrder = SortOrder FROM Hierarchy WHERE HierarchyKey = @parentFolderHierarchyKey
            SET @Run = @Run + 1
        END
        ELSE
            SET @Run = 2
    END
    SET @newSort = ((@highSort - @lowSort) / 2) + @lowSort
END



GO